package com.module.ble.db

import androidx.room.Dao
import androidx.room.Delete
import androidx.room.Insert
import androidx.room.OnConflictStrategy
import androidx.room.Query
import androidx.room.RawQuery
import androidx.room.Update
import androidx.sqlite.db.SupportSQLiteQuery
import com.module.ble.db.table.UserStepsRelatedRecord
import kotlinx.coroutines.flow.Flow

/**
 *
 * 作者：sosou
 *
 * 版本：1.0
 *
 * 创建日期：2025/04/16
 *
 * 描述：
 *
 * 修订历史：
 *
 */
@Dao
interface UserStepsRelatedRecordDao {

    @Delete
    fun delete(data: UserStepsRelatedRecord)

    @Query("SELECT * FROM steps_related_record WHERE createdTime = :time")
    fun findByTime(time : Long): UserStepsRelatedRecord?

    @Query("select * from steps_related_record")
    fun findAll(): MutableList<UserStepsRelatedRecord>

    @Query("select * from steps_related_record WHERE sysStat = 0")
    fun findAllNotUp(): MutableList<UserStepsRelatedRecord>

    @Query("SELECT * FROM steps_related_record")
    fun findAllFlow(): Flow<List<UserStepsRelatedRecord>>

    @Query("select count(*) from steps_related_record")
    fun count(): Int

    @Insert(onConflict = OnConflictStrategy.IGNORE)//新数据更新旧数据
    fun insertOrUpdate(vararg bean: UserStepsRelatedRecord)

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(mList:List<UserStepsRelatedRecord>)

    @Update
    fun update(mList:List<UserStepsRelatedRecord>)

    @Query("DELETE FROM steps_related_record")//删除所有
    fun deleteAll()

    @Query("SELECT * FROM steps_related_record ORDER BY createdTime DESC LIMIT 1")//获取数据库startTs最大值记录
    fun findLatestRecord(): UserStepsRelatedRecord?

    @Query("SELECT * FROM steps_related_record WHERE createdTime = (SELECT MAX(createdTime) FROM steps_related_record)")//性能没findLatestRecord好
    fun findLatestRecord2(): UserStepsRelatedRecord?

    // 根据传入时间戳获取该时间戳当天最新的那条数据
    @Query("SELECT * FROM steps_related_record WHERE date(createdTime/1000, 'unixepoch', 'localtime') = date(:timestamp/1000, 'unixepoch', 'localtime') ORDER BY createdTime DESC LIMIT 1")
    fun findLatestRecordByDay(timestamp: Long): UserStepsRelatedRecord?

    // 根据传入时间戳获取该时间戳当天最新的那条数据（Flow版本）
    @Query("SELECT * FROM steps_related_record WHERE date(createdTime/1000, 'unixepoch', 'localtime') = date(:timestamp/1000, 'unixepoch', 'localtime') ORDER BY createdTime DESC LIMIT 1")
    fun findLatestRecordByDayFlow(timestamp: Long): Flow<UserStepsRelatedRecord?>

    // 根据传入时间戳查询该时间戳当天的所有数据
    @Query("SELECT * FROM steps_related_record WHERE date(createdTime/1000, 'unixepoch', 'localtime') = date(:timestamp/1000, 'unixepoch', 'localtime') ORDER BY createdTime ASC")
    fun findByDay(timestamp: Long): List<UserStepsRelatedRecord>

    // 根据传入时间戳查询该时间戳当天的所有数据（Flow版本）
    @Query("SELECT * FROM steps_related_record WHERE date(createdTime/1000, 'unixepoch', 'localtime') = date(:timestamp/1000, 'unixepoch', 'localtime') ORDER BY createdTime ASC")
    fun findByDayFlow(timestamp: Long): Flow<List<UserStepsRelatedRecord>>

    @RawQuery
    fun findByCustomQuery(query: SupportSQLiteQuery): List<UserStepsRelatedRecord>


    /**
     * 将所有数据的sysStat更新为0
     */
    @Query("UPDATE steps_related_record SET sysStat = 0")
    fun updateAllSysStatToZero()

    /**
     * 删除所有step或distance小于0的无效数据
     */
    @Query("DELETE FROM steps_related_record WHERE step < 0 OR distance < 0")
    fun deleteInvalidStepsData()
}